package cc.messcat.dao.helpTeam;

import java.util.List;
import java.util.Map;

import javax.xml.crypto.dsig.Transform;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import cc.messcat.common.dao.BaseHibernateDaoUtil;
import cc.messcat.common.util.BeanUtil;
import cc.messcat.entity.AreaVO;
import cc.messcat.entity.area.Area;
import cc.messcat.entity.helpPerson.HelpPerson;
import cc.messcat.entity.helpUnits.HelpTeam;
import cc.messcat.entity.helpUnits.HelpTeamPerson;

@Repository("helpTeamDao")
public class HelpTeamDaoImpl extends BaseHibernateDaoUtil implements HelpTeamDao {
    @Override
    public HelpTeam getTeamByCondition(HelpTeam team) {
        Criteria criteria = this.getCurrentSession().createCriteria(HelpTeam.class);
        if (BeanUtil.isValid(team)) {
            if (BeanUtil.isValid(team.getId()))
                criteria.add(Restrictions.eq("id", team.getId()));
            if (BeanUtil.isValid(team.getUnit()) && BeanUtil.isValid(team.getUnit().getId()))
                criteria.add(Restrictions.eq("unit.id", team.getUnit().getId()));
            if (BeanUtil.isValid(team.getVillage()) && BeanUtil.isValid(team.getVillage().getId()))
                criteria.add(Restrictions.eq("village.id", team.getVillage().getId()));
        }
        return (HelpTeam) criteria.uniqueResult();
    }

    @Override
    @Deprecated
    public List<HelpTeamPerson> getPersonByCondition(HelpTeamPerson helpTeamPerson) {
        String sql = "from HelpTeamPerson htp where htp.status = '1' ";
        if (BeanUtil.isValid(helpTeamPerson) && BeanUtil.isValid(helpTeamPerson.getTeam()) && BeanUtil.isValid(helpTeamPerson.getTeam().getVillage()) && BeanUtil.isValid(helpTeamPerson.getTeam().getVillage().getId())) {
            sql += " and htp.team.village.name = :name ";
        }

        Query query = this.getCurrentSession().createQuery(sql);

        if (BeanUtil.isValid(helpTeamPerson) && BeanUtil.isValid(helpTeamPerson.getTeam()) && BeanUtil.isValid(helpTeamPerson.getTeam().getVillage()) && BeanUtil.isValid(helpTeamPerson.getTeam().getVillage().getId())) {
            Criteria areaCriteria = this.getCurrentSession().createCriteria(Area.class);
            areaCriteria.add(Restrictions.eq("id", helpTeamPerson.getTeam().getVillage().getId()));
            Area area = (Area) areaCriteria.uniqueResult();
            query.setString("name", area.getArea());
        }

        return query.list();
    }

    @Override
    public List<HelpTeamPerson> getPersonByVillageIds(HelpTeamPerson helpTeamPerson, Long[] villageIds) {
        String sql = "from HelpTeamPerson htp where htp.status = '1' ";
        if (villageIds != null)
            sql += "and htp.team.village.id in :villageIds ";
        Query query = getCurrentSession().createQuery(sql);
        if (villageIds != null)
            query.setParameterList("villageIds", villageIds);
        return query.list();
    }

    @Override
    public List<HelpPerson> getHelpPresonsNotInTeam(Long unitId, Long villageId) {
        Query query = this.getCurrentSession().createQuery("select hp from HelpPerson hp  where hp.id not in (select htp.helpPerson.id from HelpTeamPerson htp where htp.team.village.id = :villageId and htp.status='1') and hp.helpUnit = :unitId  AND status = '1' ");
        query.setLong("unitId", unitId);
        query.setLong("villageId", villageId);
        return query.list();
    }

    @Override
    public List<HelpTeamPerson> getPersonsByCondition(HelpTeamPerson helpTeamPerson, AreaVO areaVO) {
        Criteria criteria = this.getCurrentSession().createCriteria(HelpTeamPerson.class);
        if (BeanUtil.isValid(areaVO)) {
            criteria.createAlias("team", "ht");
            criteria.createAlias("team.village", "pv");
            if (BeanUtil.isValid(areaVO.getProvince())) {
                criteria.add(Restrictions.eq("pv.province", areaVO.getProvince()));
            }
            if (BeanUtil.isValid(areaVO.getCity()))
                criteria.add(Restrictions.eq("pv.city", areaVO.getCity()));
            if (BeanUtil.isValid(areaVO.getCry()))
                criteria.add(Restrictions.eq("pv.cry", areaVO.getCry()));
            if (BeanUtil.isValid(areaVO.getCty()))
                criteria.add(Restrictions.eq("pv.cty", areaVO.getCty()));
            if (BeanUtil.isValid(areaVO.getName()))
                criteria.add(Restrictions.eq("pv.name", areaVO.getName()));
        }
            criteria.add(Restrictions.eq("status", "1"));
        return criteria.list();
    }

	@Override
	public List<HelpTeamPerson> getPersonByCondition(Integer pageNo, Integer pageSize, HelpTeamPerson helpTeamPerson, String areaCode,Map<String,String> condition) {
		StringBuilder hql = new StringBuilder(" from HelpTeamPerson as h where status = 1 and h.team.village.area.areaCode like '"+ areaCode +"' ");
		if(condition != null && StringUtils.isNotBlank(condition.get("village"))) {
			Query q = getCurrentSession().createQuery( " from Area as a where a.areaCode like '"+ areaCode +"' and a.area like :area ");
			q.setString("area", condition.get("village"));
			Area area = (Area) (q.list().isEmpty() ? null : q.list().get(0));
			
			if(area != null && area.getAreaCode().contains(areaCode.substring(0,9))) {
				hql.append(" and areaCode like '"+ area.getAreaCode() +"'");
			} 
		}
		if(condition != null && condition.get("villageId") != null) {
			hql.append(" and h.team.village.area.id =" + condition.get("villageId"));
		}
		return getCurrentSession().createQuery(hql.toString()).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getHelpTeamPersonByPoorVillage(Long id) {
		return getCurrentSession().createSQLQuery("SELECT h.id as id,htp.start_date as startTime, htp.end_date as endTime,htp.captain as catpain,htp.first_secretary as secretary,ID_number as idNumber,Job_level as position,Modifier as modifier,Prepared_by as preparedBy,"
			+ " Prepared_time as preparedTime,date as date,duty_id as number,education as education,face as political,name as name ,sex as gender,technical as technical,"
			+ " telephone_number as tel,update_time as updateTime,HELP_UNIT as helpUnitId,Affiliation as affiliation,Unit_name as unitName "
			+ " from helpperson as h, help_team_person as htp where h.status='1' and person_id=h.id and htp.team_id in(select id from help_team where village_id =:id)")
		.setParameter("id", id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
}
